﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Web.Caching;
using System.Web;

namespace Keydet.GsaPerDiem
{
    public class Manager
    {
        public static readonly string connStringName = "Keydet.GsaPerDiem.Properties.Settings.DataConnection";
        public static readonly string connString = ConfigurationManager.ConnectionStrings[connStringName].ConnectionString;

        public Manager()
        {   
        }

        public List<StateGadgetUsage> GetGadgetLookupsByState()
        { 
            SqlConnection cn = new SqlConnection(connString);
            string sql =
                @"  SELECT      StateRequested, 
                                COUNT(StateRequested) AS LookupCount
                    FROM        GadgetUsage
                    GROUP BY    StateRequested";
                
            SqlCommand cmd = new SqlCommand(sql, cn);

            List<StateGadgetUsage> results = new List<StateGadgetUsage>();
            StateGadgetUsage sgu;
        
            cn.Open();
        
            using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (dr.Read())
                {
                    sgu = new StateGadgetUsage();
                    sgu.State = dr.GetString(0);
                    sgu.Count = dr.GetInt32(1);

                    results.Add(sgu);
                }
            }

            return results;
        }

        public void LogGadgetUsage(string state, string ipAddress)
        {
            SqlConnection cn = new SqlConnection(connString);

            string sql =
                @"  INSERT INTO GadgetUsage 
                                (StateRequested,
                                IpAddress)
                    VALUES      (@State,
                                @IpAddress)";

            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.Parameters.Add("@State", state);
            cmd.Parameters.Add("@IpAddress", ipAddress);

            cn.Open();
            cmd.ExecuteNonQuery();

            cn.Close();
        }

        public List<Rate> GetRatesByState(string state)
        {
            string PER_DIEM_RATES_FOR_STATE = "PER_DIEM_RATES_" + state.ToUpper();

            List<Rate> rates;

            rates = (List<Rate>)HttpContext.Current.Cache[PER_DIEM_RATES_FOR_STATE];

            if (rates == null)
            {
                SqlConnection cn = new SqlConnection(connString);
                string sql =
                    @"  SELECT      State, 
                                    Destination, 
                                    County, 
                                    BeginDate, 
                                    EndDate, 
                                    Lodging, 
                                    MIE, 
                                    Total 

                        FROM        dbo.Rates 
                        WHERE       State='NOTLISTED' 
                        OR          State=@State
                        ORDER BY    FirstRowIndicator DESC, State, County";

                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.Parameters.Add("@State", state);
                //SqlCacheDependency dep = new SqlCacheDependency(cmd);

                rates = new List<Rate>();
                Rate r;

                cn.Open();

                using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        r = new Rate();

                        //SEE COMMENTS IN EmptyStringToNbsp() -- it's a hack.
                        r.State = EmptyStringToNbsp(dr.GetString(0));
                        r.Destination = EmptyStringToNbsp(dr.GetString(1));
                        r.County = EmptyStringToNbsp(dr.GetString(2));
                        if (dr.IsDBNull(3))
                        {
                            r.BeginDate = EmptyStringToNbsp(string.Empty);
                        }
                        else
                        {
                            r.BeginDate = EmptyStringToNbsp(StripTime(dr.GetDateTime(3).ToString()));
                        }

                        if (dr.IsDBNull(4))
                        {
                            r.EndDate = EmptyStringToNbsp(string.Empty);
                        }
                        else
                        {
                            r.EndDate = EmptyStringToNbsp(StripTime(dr.GetDateTime(4).ToString()));
                        }
                        r.Lodging = EmptyStringToNbsp(dr.GetDecimal(5).ToString("c"));
                        r.MIE = EmptyStringToNbsp(dr.GetDecimal(6).ToString("c"));
                        r.Total = EmptyStringToNbsp(dr.GetDecimal(7).ToString("c"));

                        rates.Add(r);
                    }
                }

                //NOTE: Waiting to get SqlCacheDependeny working from winisp side.
                // For now, we'll cache for a day.  When this happens need to uncomment
                // SqlCacheDependency code above and change the Cache.Insert code.
                //HttpContext.Current.Cache.Insert(PER_DIEM_RATES_FOR_STATE, rates, dep);
                HttpContext.Current.Cache.Insert(PER_DIEM_RATES_FOR_STATE, rates,
                                                 null,
                                                 DateTime.Now.AddDays(1),
                                                 Cache.NoSlidingExpiration);
            }            

            return rates;
        }

        private string EmptyStringToNbsp(string s)
        {
            // This is a bit of a hack, but it was the quickest & easiest way
            // to fix a bug in the gadget where columns with out data weren't getting
            // the appropriate css applied because the ListView wasn't adding a <td>
            s = s.Trim();

            if (s == string.Empty)
            {
                return "&nbsp;";
            }

            return s;
        }

        private string StripTime(string timeString)
        {
            timeString = timeString.Trim();
            if (timeString != string.Empty)
            {
                return timeString.Substring(0, timeString.IndexOf(" "));
            }
            else
            {
                return timeString;
            }
        }

        public List<State> GetStates()
        {
            const string PER_DIEM_CACHED_STATES = "PER_DIEM_CACHED_STATES";            

            List<State> states;

            states = (List<State>)HttpContext.Current.Cache[PER_DIEM_CACHED_STATES];

            if (states == null)
            {
                states = new List<State>();
                State s = new State();
                s.Name = Properties.Settings.Default.StateSelectionString;
                states.Add(s);

                SqlConnection cn = new SqlConnection(connString);

                SqlCommand cmd = new SqlCommand("SELECT Name FROM dbo.States", cn);

                //SqlCacheDependency dep = new SqlCacheDependency(cmd);

                cn.Open();

                using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        s = new State();

                        s.Name = dr.GetString(0);

                        states.Add(s);
                    }
                }

                //NOTE: Waiting to get SqlCacheDependeny working from winisp side.
                // For now, we'll cache for a day.  When this happens need to uncomment
                // SqlCacheDependency code above and change the Cache.Insert code.
                //HttpContext.Current.Cache.Insert(PER_DIEM_CACHED_STATES, states, dep);
                HttpContext.Current.Cache.Insert(PER_DIEM_CACHED_STATES, states,
                                                 null,
                                                 DateTime.Now.AddDays(1),
                                                 Cache.NoSlidingExpiration);
            }

            return states;
        }
    }
}
